package main 

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type Person struct {
	UserId int `db:"user_id"`
	UserName string `db:"username"`
	Sex string `db:"sex"`
	Email string `db:"email"`
}

type Place struct {
	Country string `db:"country"`
	City string `db:"city"`
	TelCode int `db:"telcode"`
}

var Db *sqlx.DB


func init(){
	database,err :=sqlx.Open("mysql","root:l19970213.@tcp(127.0.0.1:3306)/test")
	if err !=nil {
		fmt.Println("open mysql failed",err)
		return 
	}else {
		fmt.Println("mysql 數據庫連接成功")
	}

	Db=database
	// defer Db.Close()
}

func main(){
 Trancommit()
}


func Insert(){

	r,err:=Db.Exec("insert into person(username,sex,email)values(?,?,?)","stu001","man","stu01@qq.com")
	if err !=nil {
		fmt.Println("exec failed",err)
		return
	}

	id,err:=r.LastInsertId()
	if err !=nil {
		fmt.Println("exec failed",err)
		return
	}

	fmt.Println("insert success:",id)
}

func Select(){
 var person []Person
   err :=Db.Select(&person,"select * from person")
   if err!=nil {
	   fmt.Println("exec failed",err)
	   return
   }

   fmt.Println("select success :",person)
}

func Update(){
   res,err :=Db.Exec("update person set username=? where user_id=?","student003",1)
    if err!=nil {
	   fmt.Println("exec failed",err)
	   return
   }

   row,err :=res.RowsAffected()

   if err!=nil {
	   fmt.Println("rows failed",err)
	   return
   }

   fmt.Println("update success:",row)

}


func Delete(){
    res,err :=Db.Exec("delete from person where user_id=?",1)
	if err!=nil {
		fmt.Println("Delete error:",err)
		return
	}

	row,err :=res.RowsAffected()
	fmt.Println(row)
	if err !=nil {
		fmt.Println("rows failed:",err)
		return
	}

	fmt.Println("delete success:",row)

}

// 1. MySQL事务
// mysql事务特性：

//     1) 原子性
//     2) 一致性
//     3) 隔离性
//     4) 持久性
// golang MySQL事务应用：

//     1） import (“github.com/jmoiron/sqlx")
//     2)  Db.Begin()        开始事务
//     3)  Db.Commit()        提交事务
//     4)  Db.Rollback()     回滚事务
//mysql事務
func Trancommit(){
   conn,err :=Db.Begin()
   if err!=nil {
	   fmt.Println("begin failed:",err)
	   return
   }

   r,err :=conn.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
   if err !=nil {
	   fmt.Println("exec failed:",err)
	   conn.Rollback()
	   return
   }
   id ,err :=r.LastInsertId()
   if err != nil {
	   fmt.Println("exec failed:",err)
	   conn.Rollback()
	   return
   }
   fmt.Println("insert success:",id)
    
   r,err =conn.Exec("insert into aperson(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
   if err !=nil {
	   fmt.Println("exec failed:",err)
	   conn.Rollback()
	   return
   }
   id ,err =r.LastInsertId()
   if err != nil {
	   fmt.Println("exec failed:",err)
	   conn.Rollback()
	   return
   }
   fmt.Println("insert success:",id)
    
   conn.Commit()

} 